import pymysql
from todo import Todo

class Todomanage_mysql:
    @staticmethod
    def get_connection():
        return pymysql.Connection(host='192.168.200.132',database='todolist',user='1249',password='zts@1249ZTS',autocommit=True)

    def create_table(self):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("""
            create table if not exists todos(
            id int primary key auto_increment,
            title varchar(200),
            completed int default 0
            );
            """)
    def create(self,title):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("insert into todos(title) values(%s)",(title,))
            cursor.execute("select last_insert_id()")
            (id,) = cursor.fetchone()
        return Todo(title,id)

    def get_list(self):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("select * from todos")
            todos_list = cursor.fetchall()
            todos = []
            for row in todos_list:
                todos.append(Todo(row[1],row[0],row[2]))
            return todos

    def get_todo_byid(self,id):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("select * from todos where id=%s", (id,))
            todo = cursor.fetchone()
            if todo is not None:
                return Todo(todo)

    def mark_completed(self,id):
        todo = self.get_todo_byid(id)
        if todo is not None:
            with self.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute("update todos set completed=1 where id=%s",(id,))
                data = cursor.fetchone()
        return Todo(data)

    def delete(self,id):
        todo = self.get_todo_byid(id)
        if todo is not None:
            with self.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute("delete from todos where id=%s", (id,))
            return todo


